package com.gzx.plugin.tjzy.modular.call.receipt.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gzx.plugin.tjzy.modular.call.ht.entity.vo.GzxCallReportVo;
import com.gzx.plugin.tjzy.modular.call.receipt.bo.GzxReceiptBusinessStatisticBo;
import com.gzx.plugin.tjzy.modular.call.receipt.entity.GzxHtCallReceiptBusiness;
import com.gzx.plugin.tjzy.modular.call.receipt.vo.GzxHtCallReceiptBusinessVo;
import com.gzx.plugin.tjzy.modular.call.receipt.vo.GzxHtCallReportVo;
import com.gzx.plugin.tjzy.modular.call.receipt.vo.GzxReceiptBusinessStatisticVo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.common.mapper.BaseMapperPlus;
import vip.xiaonuo.common.pojo.ExcelCalleeVo;

import java.util.List;

/**
 * @author zjt
 * @description
 * @since 2025/3/28 18:28
 */
public interface GzxHtCallReceiptBusinessMapper extends BaseMapperPlus<GzxHtCallReceiptBusiness, GzxHtCallReceiptBusinessVo> {


    /**
     * 批量保存恒天话单
     *
     * @param valSql
     */
    @Insert("<script>" +
            "INSERT INTO gzx_ht_call_receipt_business(" +
            "id,business, call_type, call_id, agent_num, user_name, caller, callee, " +
            "ring_time, ring_duration, connect_time, start_time, end_time, call_duration, " +
            "file_url, area, result, `signal`, task_id, import_time, add_time, acd_end_time, " +
            "acd_duration, invite_comments, myd_key, asr_code, asr_name, company_name, " +
            "case_id,tenant_id,dept_id,created_time) " +
            "VALUES ${valSql}" +
            "</script>")
    void insertBatchByValSql(String valSql);

    /**
     * 统计坐席工作报表
     *
     * @param page
     * @param createDate
     * @param deptId
     * @return
     */
    @Select("<script>" +
            "select agent_num as agentNum," +
            "count(if(call_duration > 0, 1, null)) as connectCalls," +
            "count(1) as totalCalls," +
            "sum(call_duration) as callDuration," +
            "count(if(call_duration >= 15, 1, null)) as validConnected," +
            "sum(if(call_duration >= 15, call_duration, null)) as validCallDuration " +
            "from gzx_ht_call_record force index (create_time_idx) " +
            "where created_time BETWEEN CONCAT(#{createDate}, ' 00:00:00') AND CONCAT(#{createDate}, ' 23:59:59') " +
            "and dept_id = #{deptId}" +
            "group by agent_num " +
            "</script>")
    Page<GzxHtCallReportVo> pageCallReportOld(@Param("page") Page<GzxCallReportVo> page, @Param("createDate") String createDate, Long deptId);

    /**
     * 统计坐席工作报表
     *
     * @param page
     * @param createDate
     * @param deptId
     * @return
     */
    @Select("<script>" +
            "select agent_num as agentNum," +
            "count(if(call_duration > 0, 1, null)) as connectCalls," +
            "count(1) as totalCalls," +
            "sum(call_duration) as callDuration," +
            "count(if(call_duration >= 15, 1, null)) as validConnected," +
            "sum(if(call_duration >= 15, call_duration, null)) as validCallDuration " +
            "from gzx_ht_call_receipt_business force index (create_time_idx) " +
            "where created_time BETWEEN CONCAT(#{createDate}, ' 00:00:00') AND CONCAT(#{createDate}, ' 23:59:59') " +
            "and dept_id = #{deptId}" +
            "group by agent_num " +
            "</script>")
    Page<GzxHtCallReportVo> pageCallReport(@Param("page") Page<GzxCallReportVo> page, @Param("createDate") String createDate, Long deptId);

    //手机号拨打次数大于5次的坐席，被叫号码
    @Select({
            "<script>",
            "SELECT call_type as callType, agent_num as agentNum,aes_decrypt(from_base64(callee), 'mhxzkhl@2024gzx.') as callee, count(*) as cnt,dept_id from gzx_ht_call_receipt_business " +
                    " WHERE created_time BETWEEN CONCAT(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d'),' 00:00:00') and CONCAT(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d'),' 23:59:59') " +
                    " GROUP BY agent_num,callee,call_type ,dept_id " +
                    " HAVING count(*) > 5 " +
                    "order by 4 desc " +
                    "</script>"
    })
    List<ExcelCalleeVo> getCalleeList();

    //线路接通率
    @Select({
            "<script>",
            "SELECT  caller, " +
                    "  count(if(ring_duration> 0, 1, null)) as connectRing, " +
                    "  count(if(call_duration > 0, 1, null)) as connectCall, " +
                    "  count(*) as totalCall " +
                    " FROM gzx_ht_call_receipt_business " +
                    " WHERE created_time BETWEEN #{beginTime} and #{endTime} " +
                    " and call_type = 1 " +
                    " <if test='caller != null and caller != \"\"'>" +
                    "   and caller = #{caller}" +
                    " </if>" +
                    " GROUP BY caller " +
                    " LIMIT #{current}, #{size}" +
                    "</script>"
    })
    List<GzxReceiptBusinessStatisticVo> callerRate(GzxReceiptBusinessStatisticBo bo);

    //线路接通率总数
    @Select({
            "<script>",
            "select count(*) from (" +
                    " SELECT  caller, " +
                    "  count(if(call_duration > 0, 1, null)) as connectCall, " +
                    "  count(*) as totalCall " +
                    " FROM gzx_ht_call_receipt_business " +
                    " WHERE created_time BETWEEN #{beginTime} and #{endTime} " +
                    " and call_type = 1 " +
                    " <if test='caller != null and caller != \"\"'>" +
                    "   and caller = #{caller}" +
                    " </if>" +
                    " GROUP BY caller " +
                    " ) t" +
                    "</script>"
    })
    long callerRateTotal(GzxReceiptBusinessStatisticBo bo);
}
